package com.zzzzzz.app.topic.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zzzzzz.app.topic.NoSuchCommentException;
import com.zzzzzz.app.topic.model.bo.CommentBo;
import com.zzzzzz.app.topic.model.po.Comment;
import com.zzzzzz.common.util.Validator;
import com.zzzzzz.core.dao.BaseDao;
import com.zzzzzz.core.exception.SystemException;

@Repository
public class CommentDao extends BaseDao {
	
	private static final String ADD = "insert into comment_(comment_id, topic_id, user_id, content, parent_id, create_date, close_status) values(:commentId, :topicId, :userId, :content, :parentId, :createDate, :closeStatus)";
	
	private static final String DELETE = "delete from comment_ where comment_id = ?";
	
	private static final String FIND_BY_ID = "select * from comment_ where comment_id = ?";
	
	private static final String FIND_COUNT_BY_TOPIC_ID = "SELECT count(*) FROM comment_ WHERE topic_id = ? AND parent_id = 0";

	private static final String _NO_SUCH_ENTITY_WITH_PRIMARY_KEY = "No Comment exists with the primary key ";
	
	protected class CommentRowMapper implements RowMapper<Comment> {
		public Comment mapRow(ResultSet rs, int rn) throws SQLException {
			Comment comment = new Comment();
			comment.setCommentId(rs.getLong("comment_id"));
			comment.setTopicId(rs.getLong("topic_id"));
			comment.setUserId(rs.getLong("user_id"));
			comment.setContent(rs.getString("content"));
			comment.setParentId(rs.getLong("parent_id"));
			comment.setCreateDate(rs.getTimestamp("create_date"));
			comment.setCloseStatus(rs.getBoolean("close_status"));
			return comment;
		}
	}
	
	protected class CommentBoRowMapper implements RowMapper<CommentBo> {
		public CommentBo mapRow(ResultSet rs, int rn) throws SQLException {
			Comment comment = new Comment();
			comment.setCommentId(rs.getLong("comment_id"));
			comment.setTopicId(rs.getLong("topic_id"));
			comment.setUserId(rs.getLong("user_id"));
			comment.setContent(rs.getString("content"));
			comment.setParentId(rs.getLong("parent_id"));
			comment.setCreateDate(rs.getTimestamp("create_date"));
			comment.setCloseStatus(rs.getBoolean("close_status"));
			CommentBo commentBo = new CommentBo(comment);
			commentBo.setCreateBy("create_by");
			return commentBo;
		}
	}
	
	/**
	 * 
	 * Deal with the entire ResultSet
	 *
	 */
	protected class CommentExtractor implements ResultSetExtractor<List<CommentBo>> {
		public List<CommentBo> extractData(ResultSet rs) throws SQLException,DataAccessException {
			Map<Long, CommentBo> map = new HashMap<Long, CommentBo>();
			CommentBo pCommentBo = null;
			while (rs.next()) {
				Long pCommentId = rs.getLong("p_comment_id");
				pCommentBo = map.get(pCommentId);
				if (pCommentBo == null) {
					Comment pComment = new Comment();
					pComment.setCommentId(pCommentId);
					pComment.setTopicId(rs.getLong("p_topic_id"));
					pComment.setUserId(rs.getLong("p_user_id"));
					pComment.setContent(rs.getString("p_content"));
					pComment.setParentId(rs.getLong("p_parent_id"));
					pComment.setCreateDate(rs.getTimestamp("p_create_date"));
					pComment.setCloseStatus(rs.getBoolean("p_close_status"));
					pCommentBo = new CommentBo(pComment);
					pCommentBo.setCreateBy(rs.getString("p_create_by"));
					map.put(pCommentId, pCommentBo);
				}
				
				Long commentId = rs.getLong("comment_id");
				if(Validator.isNotNull(commentId)) {
					Comment comment = new Comment();
					comment.setCommentId(commentId);
					comment.setTopicId(rs.getLong("topic_id"));
					comment.setUserId(rs.getLong("user_id"));
					comment.setContent(rs.getString("content"));
					comment.setParentId(rs.getLong("parent_id"));
					comment.setCreateDate(rs.getTimestamp("create_date"));
					comment.setCloseStatus(rs.getBoolean("close_status"));
					CommentBo commentBo = new CommentBo(comment);
					commentBo.setCreateBy(rs.getString("create_by"));
					pCommentBo.getChildCommentBoList().add(commentBo);
				}
			}
			return new ArrayList<CommentBo>(map.values());
		}
	}
	
	public Comment add(Comment comment) throws SystemException {
		try{
			long commentId = updateForLongKey(ADD, comment);
			comment.setCommentId(commentId);
			return comment;
		}catch (Exception e) {
			throw processException(e);
		}
	}
	
	public void delete(Long commentId) throws SystemException {
		try{
			update(DELETE, commentId);
		}catch (Exception e) {
			throw processException(e);
		}
	}
	
	public Comment findById(Long id) throws NoSuchCommentException, SystemException {
		Comment comment = null;
		try{
			comment = findOne(FIND_BY_ID, new CommentRowMapper(), id);
		}catch(Exception e) {
			throw processException(e);
		}
		if(comment == null) {
			throw new NoSuchCommentException(_NO_SUCH_ENTITY_WITH_PRIMARY_KEY + id);
		}
		return comment;
	}

	public int findCountByParentId(Long parentId) throws SystemException {
		try{
			String sql = "SELECT count(*) FROM comment_ c WHERE c.parent_id = ?";
			return findInt(sql, parentId);
		}catch(Exception e) {
			throw processException(e);
		}
	}
	
	public List<Comment> findAllCommentsByParentId(long parentId) throws SystemException {
		try{
			String sql = "SELECT c.* FROM comment_ c WHERE c.parent_id = ?";
			return findList(sql, new CommentRowMapper(), parentId);
		}catch(Exception e) {
			throw processException(e);
		}
	}
	
	public List<CommentBo> findCommentBoListByParentId(long parentId, boolean ascending, int start, int offset) throws NoSuchCommentException, SystemException {
		try{
			String sql = "SELECT u.username AS create_by, c.* FROM comment_ c INNER JOIN user_ u ON c.user_id = u.user_id " +
					"WHERE c.parent_id = ? ORDER BY c.create_date ";
			
			if(ascending) {
				sql += "ASC ";
			}else {
				sql += "DESC ";
			}
			
			sql += "LIMIT ?, ?";

			return findList(sql, new CommentBoRowMapper(), parentId, start, offset);
		}catch(Exception e) {
			throw processException(e);
		}
	}

	public int findCountByTopicId(Long topicId) throws SystemException {
		try{
			return findInt(FIND_COUNT_BY_TOPIC_ID, topicId);
		}catch(Exception e) {
			throw processException(e);
		}
	}
	
	public List<CommentBo> findByTopicId(long topicId, boolean pAscending, int pStart, int pOffset, boolean cAscending, 
		int cStart, int cOffset) throws SystemException {
		try{
			String sql = "SELECT pc.create_by AS p_create_by, pc.comment_id AS p_comment_id, pc.topic_id AS p_topic_id, " +
					"pc.user_id AS p_user_id, pc.content AS p_content, pc.parent_id AS p_parent_id, pc.create_date AS p_create_date, " +
					"pc.close_status AS p_close_status, cc.* FROM (SELECT u.username AS create_by, c.* " +
					"FROM topic t INNER JOIN comment_ c ON t.topic_id = c.topic_id INNER JOIN user_ u ON c.user_id = u.user_id " +
					"WHERE t.topic_id = ? AND c.parent_id = 0 ORDER BY c.create_date ";
			
			if(pAscending) {
				sql += "ASC ";
			}else {
				sql += "DESC ";
			}
			
			sql += "LIMIT ?, ?) pc LEFT JOIN (SELECT u.username AS create_by, c.* FROM comment_ c INNER JOIN user_ u " +
					"ON c.user_id = u.user_id ORDER BY c.create_date ";
			
			if(cAscending) {
				sql += "ASC ";
			}else {
				sql += "DESC ";
			}
			
			sql += "LIMIT ?, ?) cc ON pc.comment_id = cc.parent_id";
			
			return (List<CommentBo>) jdbcTemplate.getJdbcOperations().query(sql, new CommentExtractor(), topicId, pStart, pOffset, cStart, cOffset);
		}catch(Exception e) {
			throw processException(e);
		}
	}

	
}
